﻿using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using SouthApps.MobileHealth.SDK.DAL.Model;
using System.Data.Objects;

namespace SouthApps.MobileHealth.SDK.DAL.DataAccess
{
    public class ProvinciaDao : DataAccessObject
    {
        #region Add

        public void AddProvincia(Provincia provincia)
        {
            try
            {
                using (var entities = this.EntityContext)
                {
                    entities.AddToprovincia(provincia);

                    this.SaveChanges(entities);
                }
            }
            catch (Exception ex)
            {
                throw this.HandleException(ex);
            }
        }

        #endregion

        #region Modify

        public void ModifyProvincia(Provincia provincia)
        {
            try
            {
                using (var entities = this.EntityContext)
                {
                    Provincia oldProvincia = entities.provincia.FirstOrDefault(e => e.provinciaId == provincia.provinciaId);
                    oldProvincia = provincia;

                    this.SaveChanges(entities);
                }
            }
            catch (Exception ex)
            {
                throw this.HandleException(ex);
            }
        }

        #endregion

        #region Get

        public List<Provincia> GetProvinciasConSucursalesPrestador(int financiadorId, int paisId)
        {
            try
            {
                using (var entities = this.EntityContext)
                {
                    var query = ((from e in entities.departamento
                                  from p in entities.provincia
                                  join sp in entities.sucursalprestador.Include("Prestador") on e.departamentoId equals sp.departamentoId
                                  join co in entities.convenio on sp.sucursalPrestadorId equals co.sucursalPrestadorId
                                  where sp.prestador.financiadorId == financiadorId && e.provinciaId == p.provinciaId && p.paisId == paisId
                                  select p) as ObjectQuery<Provincia>).Include("Pais").Distinct();
                    return query.ToList();
                }
            }
            catch (Exception ex)
            {
                throw this.HandleException(ex);
            }
        }

        public List<Provincia> GetProvinciasConEspecialidadSucursalesPrestador(int financiadorId, int paisId, int especialidadId)
        {
            try
            {
                using (var entities = this.EntityContext)
                {
                    var query = ((from e in entities.departamento
                                  from p in entities.provincia
                                  join sp in entities.sucursalprestador.Include("Prestador") on e.departamentoId equals sp.departamentoId
                                  join co in entities.convenio on sp.sucursalPrestadorId equals co.sucursalPrestadorId
                                  where co.especialidadId == especialidadId
                                  where sp.prestador.financiadorId == financiadorId && e.provinciaId == p.provinciaId && p.paisId == paisId
                                  select p) as ObjectQuery<Provincia>).Include("Pais").Distinct();
                    return query.ToList();
                }
            }
            catch (Exception ex)
            {
                throw this.HandleException(ex);
            }
        }

        public List<Provincia> GetProvinciasConGuardiasSucursalesPrestador(int financiadorId, int paisId, int? servicioGuardiaId)
        {
            try
            {
                using (var entities = this.EntityContext)
                {
                    var query = ((from e in entities.departamento
                                  from p in entities.provincia
                                  join sp in entities.sucursalprestador.Include("Prestador") on e.departamentoId equals sp.departamentoId
                                  join co in entities.convenio on sp.sucursalPrestadorId equals co.sucursalPrestadorId
                                  join gsp in entities.guardiasucursalprestador on sp.sucursalPrestadorId equals gsp.sucursalPrestadorId
                                  where sp.prestador.financiadorId == financiadorId && e.provinciaId == p.provinciaId && p.paisId == paisId
                                  where servicioGuardiaId==null || gsp.servicioGuardiaId == servicioGuardiaId
                                  select p) as ObjectQuery<Provincia>).Include("Pais").Distinct();

                    return query.ToList();
                }
            }
            catch (Exception ex)
            {
                throw this.HandleException(ex);
            }
        }

        public List<Provincia> GetProvinciasConServicioGuardia(int financiadorId, int paisId, int servicioGuardiaId)
        {
            try
            {
                using (var entities = this.EntityContext)
                {
                    var query = ((from e in entities.departamento
                                  from p in entities.provincia
                                  join sp in entities.sucursalprestador.Include("Prestador") on e.departamentoId equals sp.departamentoId
                                  join co in entities.convenio on sp.sucursalPrestadorId equals co.sucursalPrestadorId
                                  join gsp in entities.guardiasucursalprestador on sp.sucursalPrestadorId equals gsp.sucursalPrestadorId
                                  where sp.prestador.financiadorId == financiadorId && e.provinciaId == p.provinciaId && p.paisId == paisId
                                  where gsp.servicioGuardiaId == servicioGuardiaId
                                  select p) as ObjectQuery<Provincia>).Include("Pais").Distinct();

                    return query.ToList();
                }
            }
            catch (Exception ex)
            {
                throw this.HandleException(ex);
            }
        }

        public List<Provincia> GetProvinciasConServicioEspecial(int financiadorId, int paisId, int servicioEspecialId)
        {
            try
            {
                using (var entities = this.EntityContext)
                {
                    var query = ((from e in entities.departamento
                                  from p in entities.provincia
                                  join se in entities.locacionservicioespecial on e.departamentoId equals se.departamentoId
                                  where se.financiadorId == financiadorId && e.provinciaId == p.provinciaId && p.paisId == paisId && se.servicioEspecialId == servicioEspecialId
                                  select p) as ObjectQuery<Provincia>).Include("Pais").Distinct();

                    return query.ToList();
                }
            }
            catch (Exception ex)
            {
                throw this.HandleException(ex);
            }
        }

        public List<Provincia> GetProvinciasConCentrosAtencion(int financiadorId, int paisId)
        {
            try
            {
                using (var entities = this.EntityContext)
                {
                    var query = ((from e in entities.departamento
                                  from p in entities.provincia
                                  join ca in entities.centroatencion on e.departamentoId equals ca.departamentoId
                                  where ca.financiadorId == financiadorId && e.provinciaId == p.provinciaId && p.paisId == paisId
                                  select p) as ObjectQuery<Provincia>).Include("Pais").Distinct();

                    return query.ToList();
                }
            }
            catch (Exception ex)
            {
                throw this.HandleException(ex);
            }
        }

        #endregion
    }
}
